Sql-server – Does the SQL Server Backup Database Command also backup views from a Database

backuprestoresql serverview

I have a backup job (SQL Server Agent job) which will iterate through all of our SQL Server databases in SSMS, invoke the BACKUP DATABASE command to create a .bak file on a different server. This other server has its own job iterating through all of the .baks and restoring them (with the RESTORE DATABASE command).

EDIT: The server running the RESTORE job restores the .baks into its own installation of SSMS

The jobs report success in the SQL Agent job history so I'm wondering whether or not these jobs should also backup and restore the Views in the databases (as they haven't seemed to) or if this is an indication that the scripts aren't quite working?

Best Answer

The backup command backs up views, stored procedures, user-defined functions and so on.To test:

Create a database
Create a table
Insert something in it
Create a view on this table
Restore the database with another name; or
Drop the database and restore the previous .bak

Related Solutions

SQL Server 2005 – How to Move (Restore) a Database That Does Not Exist

No, for a restore you don't have to have the database existing already (be mindful though to not have the with replace option set in your restore database command).

The below statement would create a new database from the backup:

RESTORE DATABASE [YourNewDbName] 
FROM  DISK = N'C:\YourBackupFile.bak' 
WITH  FILE = 1,  
    MOVE N'YourDataFile' TO N'C:\YourNewDbName.mdf',  
    MOVE N'YourLogFile' TO N'C:\YourNewDbName_1.ldf',  
    NOUNLOAD,  
    STATS = 10
GO

Obviously, modify the parameters accordingly. But this doesn't require YourNewDbName to exist prior to the restore database.

Sql-server – Error when restoring SQL Server 2008 R2 backup on 2012

Do a Restore Headeronly With File = '...' If it still fails it's not a valid native SQL Server backup file. It's not an issue of versions.

Open it with a hex or text editor, what's the file header look like? Maybe it's compressed with a 3rd party solution like LiteSpeed and those require you to use a tool to convert to native format if you don't have it installed in this environment.

Best Answer

Related Solutions

SQL Server 2005 – How to Move (Restore) a Database That Does Not Exist

Sql-server – Error when restoring SQL Server 2008 R2 backup on 2012

Related Question