June 26, 2009 at 4:44 am
Le's say the DB is named "Reports". I want to preserve and be able to access each year's records under a new name, such as "Reports2009". I can rename the DB in SQL Server Management, but I still have only one DB, not two. Thanks for your help.
June 26, 2009 at 4:49 am
bytesizedata (6/26/2009)
Le's say the DB is named "Reports". I want to preserve and be able to access each year's records under a new name, such as "Reports2009". I can rename the DB in SQL Server Management, but I still have only one DB, not two. Thanks for your help.
You can rename the db (sp_renamedb??) but in your case u need to archive ur data to new databases for each year may be..
June 26, 2009 at 4:52 am
Backup the database
Backup database DB_name to disk='path+filename.bak'
Restore using different name
Reastore database DB_New_name from disk='path+filename.bak'
with move datafile_name='newfilepath', logfile_name='newfilepath'
June 26, 2009 at 6:20 am
The Backup command executed OK. However, the Restore command
Restore database Records2009 from disk='path+filename.bak' with move datafile_name='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Records2009', logfile_name='C:\Program Files]Microsoft SQL Server\MSSQL.2\MSSQL\Data\Records2009'
resulted in the error messages "Incorrrect syntax near 'datafile_name'
Changing the above to MSSQL.1 also resulted in the same error message.
June 26, 2009 at 6:26 am
you should replace datafile_name and logfile_name with there logical file name in single qoutes. Also put the correct backup path.
To find out the file names execute.
Restore filelistonly from disk='backupfile'
June 26, 2009 at 6:52 am
The command
Restore filelistonly from disk='backupfile' gave me the logical file names "Records" and "Records_log"
Executing
Restore database Records2009 from disk='path+filename.bak' with move datafile_name='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records', logfile_name='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records_log'
continued to give the error message
"Incorrect syntax near 'datafile_name'"
I tried using MSSQL.1 and MSSQL.2
June 26, 2009 at 7:20 am
bytesizedata (6/26/2009)
The commandRestore filelistonly from disk='backupfile' gave me the logical file names "Records" and "Records_log"
Executing
Restore database Records2009 from disk='path+filename.bak' with move datafile_name='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records', logfile_name='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records_log'
continued to give the error message
"Incorrect syntax near 'datafile_name'"
I tried using MSSQL.1 and MSSQL.2
Restore database Records2009 from disk='BackupFilePath\filename.bak' with move
'records'='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records',
'records_log'='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records_log'
replace the portion in the bold with actual path of your backup file.
EDIT - Corrected the code.
June 26, 2009 at 7:41 am
Now I get the error message "Incorrect syntax near '='" I assume the error is at the first instance:
'Records'='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Records', etc.
Question: Don't I want this archived, renamed DB stored in folder MSSQL.2\MSSQL\Data? If stored in folder MSSQL.1, what happens to the existing DB? Or, is the renamed DB automatically put in its own folder?
June 26, 2009 at 8:35 am
I finally figured out how to restore the DB under a new name by using SQL Server Management's Tasks option. Not very intuitive, is it.
Thanks for your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply