Sunday, January 16, 2011

SQL 2008 - Backup/Restore using T-SQL


BACKUP DATABASE [TestDb] TO DISK=N'C:\download\TestDb_Adhoc_backup.bak'
WITH NAME = N'TestDb-Full Database Backup',
COPY_ONLY, NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
/*
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 17912 pages for database 'TestDb', file 'TestDb' on file 1.
100 percent processed.
Processed 1 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP DATABASE successfully processed 17913 pages in 7.380 seconds (18.962 MB/sec).
*/
BACKUP DATABASE [TestDb] TO DISK=N'C:\download\TestDb_Adhoc_compress_backup.bak'
WITH NAME = N'TestDb-Full Database Backup',
COPY_ONLY, COMPRESSION, NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
/*
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 17912 pages for database 'TestDb', file 'TestDb' on file 1.
100 percent processed.
Processed 1 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP DATABASE successfully processed 17913 pages in 5.422 seconds (25.809 MB/sec).
*/

RESTORE VERIFYONLY FROM DISK=N'C:\download\TestDb_Adhoc_backup.bak' WITH NOUNLOAD, NOREWIND
GO
/*
The backup set on file 1 is valid.
*/
ALTER DATABASE [TestDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

/*
RESTORE DATABASE [SalesDB] FROM DISK = N'C:\download\SQL08Backup.BAK' WITH FILE = 37, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [SalesDB] FROM DISK = N'C:\download\SQL08Backup.BAK' WITH FILE = 43, NOUNLOAD, STATS = 10
GO
*/
RESTORE DATABASE [TestDb] FROM DISK=N'C:\download\TestDb_Adhoc_backup.bak' WITH REPLACE, NOUNLOAD, NOREWIND, STATS = 10
GO
/*
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 17912 pages for database 'TestDb', file 'TestDb' on file 1.
Processed 1 pages for database 'TestDb', file 'TestDb_log' on file 1.
RESTORE DATABASE successfully processed 17913 pages in 4.340 seconds (32.245 MB/sec).
*/

ALTER DATABASE [TestDb] SET MULTI_USER;
GO

DBCC CHECKDB ('TestDb') WITH NO_INFOMSGS;
GO
/*
Command(s) completed successfully.
*/