How can i modify this script to backup a single database?

Question

Post reply

How can i modify this script to backup a single database?

nrocha

SSC Rookie

Points: 38
More actions
December 1, 2010 at 3:29 pm

#231767

here is the script which works, backs up all databases. I need to backup one specific database only...
-----------------------------------------------START----------------------------
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
SET @path = 'C:\Backups\'
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
WITH INIT
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
--------------------------------------END--------------------------------
Can somone Help? Hope i posted this in the right area

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

SET @path = 'C:\Backups\'

SET @name = 'Your DB Name HERE'

SET @fileName = @path + @name + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

WITH INIT

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 2

The sql code within the cursor backups the db

The cursor is running for every db in the server

So concentrate in the cursor code, except fetch, etc codes replacing the cursor parameters with target database properties

There is an other sample at MS SQL Server Backup Script

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

nrocha SSC Rookie Points: 38 More actions · Answer 3

Guys - Thank you very much! Works perfectly. Take Care