Automatic SQL Backup/Restore

polonious

Hi guys,

I'm trying to think of a good way of automating a backup/restore of a SQL database on a weekly/biweekly basis. Anybody know if this is possible through a SQL script or similar, or have any examples?

Any help would be great. Thanks.

DenSter

Sure, use the SQL Server job scheduler. Read all about it in Books Online (Start, All programs, SQL Server, Documentation and Tutorials).

polonious

I should clarify, it's a backup of a Live DB and restoring to a Test DB to keep the test environment up to date.

DenSter

DenSter wrote:

Sure, use the SQL Server job scheduler. Read all about it in Books Online (Start, All programs, SQL Server, Documentation and Tutorials).

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

stryk

In SSMS you could quickly create the require TSQL scripts - as DenSter said, you'll find the details in "Books Online" & Co..
Here an example:

BACKUP DATABASE [Navision] -- Live DB
TO  DISK = N'D:\Backup\LiveToTest.bak'
WITH  DESCRIPTION = N'Full Backup for Test DB', 
NOFORMAT, INIT,  
NAME = N'LiveToTest', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TestDB]  -- Test DB
FROM  DISK = N'D:\Backup\LiveToTest.bak' 
WITH  FILE = 1,  
MOVE N'NAV_Data'   TO N'D:\Databases\TestDB.mdf',   -- change mapping
MOVE N'NAV_1_Data' TO N'D:\Databases\TestDB_1.ndf', -- change mapping 
MOVE N'NAV_Log'    TO N'E:\Databases\TestDB_2.ldf', -- change mapping 
NOUNLOAD,  REPLACE,  STATS = 10
GO

Put something like this in a Job and execute as you like :thumbsup: