Database backup in SQLServer2008

Posted on by
Reply

1. Conventional Backup and Restore

  • Below is the command to take conventional one-file backupof Microsoft ‘AdventureWorks’ database.
BACKUP DATABASE [AdventureWorks]
 TO     DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks.bak'
 GO
  • Below is the command to restore backed up Microsoft ‘AdventureWorks’ database.
RESTORE DATABASE [AdventureWorks]
 FROM     DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks.bak'
 GO

2. Split File Backup and Restore

  • Below is the command to split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files.
BACKUP DATABASE [AdventureWorks]
 TO     DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks1.bak',
 DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks2.bak',
 DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks3.bak'
 GO
  • Below is the command to restore the split backup files.
RESTORE DATABASE [AdventureWorks]
 FROM     DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks1.bak',
 DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks2.bak',
 DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks3.bak'
 GO